

clear all
cap log close

log using "${logfiles}step3_police_workplace.log", replace

foreach Y of numlist 2000/2018{ 
use "W:\workplace\data\police_DV_workplace_defendant_`Y'.dta", clear
duplicates drop defendant_shnro, force
save "W:\workplace\data\police_DV_workplace_defendant_`Y'.dta", replace


use "W:\workplace\data\police_DV_workplace_victim_`Y'.dta", clear
duplicates drop victim_shnro, force
save "W:\workplace\data\police_DV_workplace_victim_`Y'.dta", replace
}



* Pull outcomes from the Fleed data for the years 2006-2011
forvalues i= 2006(1) 2011{
	local current = -5
	while `current' <= 5 {
	
	
	local a = `i' + `current'
	
	use "${data}fleed_kokonais_`a'.dta", clear
	compress
	
	
	if `a' < 2005 {
		keep shtun shnro vuosi sykstun syrtun ptoim1 tyotu svatva tol95xht ammattikoodi tyokk kunta ututku toimiala ika tyrtu svatvp 
	}
	
	
	
	if `a' >= 2005 {
		keep shtun shnro vuosi sykstun syrtun ptoim1 tyotu svatva toimiala ammattikoodi tyokk kunta ututku ika tyrtu svatvp
	}

	
	*Industry 
	cap rename toimiala tol95xht
	rename tyotu ttyotul
	rename ammattikoodi amko
	
	*Employment
	destring ptoim1, replace
	gen employed=ptoim1==11 & sykstun!="."

	*Education variables
	g educ=ututku 
	destring educ, replace
	gen level=substr(ututku, 1,1)
	destring level, replace
	
	
	* Field of study
	gen field=substr(ututku, 2,2)
	destring field, replace
	replace field = 0 if field == .
	

	* Municipality 
	destring kunta, g(municipality)

	* Earnings
	replace ttyotul = 0 if missing(ttyotul)
	replace tyrtu = 0 if missing(tyrtu)
	replace svatva = 0 if missing(svatva)
	rename svatvp capitalEarnings
	replace capitalEarnings = 0 if missing(capitalEarnings)

	* Sum of labor and enrepreneur earnings 
	gen allEarnings =  ttyotul + tyrtu
	
	gen anyEpreneurEarn = tyrtu > 0 


	/* Merge with baseyear, and keep only those individuals that are in the base sample */
	
  	sort shtun
	drop if mi(shtun)
	drop shnro
	fmerge 1:1 shtun using "${dataout}police_DV_workplace_defendant_`i'.dta"

	
	keep if _merge==3
    drop _merge
	
	
	gen time = `current'
	
	save "${dataout}DV_workplace_defendant`i'year`a'", replace
	local current = `current' +1 
	}
	
*** Put together data for same baseyear, 5 years before and 5 years after

	local minus1 = `i' - 1
	local minus2 = `i' - 2
	local minus3 = `i' - 3
	local minus4 = `i' - 4
	local minus5 = `i' - 5
	local plus0 = `i' 
	local plus1 = `i' + 1
	local plus2 = `i' + 2
	local plus3 = `i' + 3
	local plus4 = `i' + 4
	local plus5 = `i' + 5
	

	*APPEND FILES*
	
	use "${dataout}DV_workplace_defendant`i'year`minus5'.dta", clear
	append using "${dataout}DV_workplace_defendant`i'year`minus4'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`minus3'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`minus2'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`minus1'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`i'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`plus1'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`plus2'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`plus3'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`plus4'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`plus5'.dta"
	



	*Deflate the earnings 
	g year=vuosi
	fmerge m:1 year using  "${dataout}\cpi"
	keep if _merge == 3 
	drop _merge
	
	gen real_earn=ttyotul/cpi
	gen real_inc=svatva/cpi
	gen realEpreEarn = tyrtu/cpi
	gen realAllEarn = allEarnings/cpi
	gen realCapEarn = capitalEarnings/cpi

	
	drop cpi ttyotul svatva 
		
	*INDUSTRY AND OCCUPATION FOR EWCS DATA
	gen nace_2=real(substr(tol95xht, 1,2))

	loc vars "nace"
	foreach var in `vars' {
		g `var'=.
		replace `var'=1 if `var'_2==1 | `var'_2==2 | `var'_2==5
		replace `var'=2 if `var'_2>=10 & `var'_2<=37
		replace `var'=3 if `var'_2==40 | `var'_2==41
		replace `var'=4 if `var'_2==45  
		replace `var'=5 if `var'_2>=50 & `var'_2<=52
		replace `var'=6 if `var'_2==55  

	}

	drop nace_2 
	label var nace "Industry"


	*Occupation codes
	gen occ=real(substr(amko, 1,1))
	
	
	gen baseyr=`i'
	so time baseyr
	order shtun time baseyr
	
	rename vuosi yr	
	sort shtun yr
	compress
	save "${dataout}DV_workplace_defendant_`i'.dta", replace
		
}


**** Fleed data for 2012-2014 **** 
forvalues i= 2012(1) 2014{
	local current = -5
	while `current' <= 2016-`i' {
	
	display "Baseyear is `i'"
	local a = `i' + `current'
	
	use "${data}fleed_kokonais_`a'.dta", clear
	compress
	
	
	if `a' < 2005 {
		keep shtun shnro vuosi sykstun syrtun ptoim1 tyotu svatva tol95xht ammattikoodi tyokk kunta ututku toimiala ika tyrtu svatvp 
	}
	
	
	
	if `a' >= 2005 {
		keep shtun shnro vuosi sykstun syrtun ptoim1 tyotu svatva toimiala ammattikoodi tyokk kunta ututku ika tyrtu svatvp
	}

	
	*Industry 
	cap rename toimiala tol95xht
	rename tyotu ttyotul
	rename ammattikoodi amko
	
	*Employment
	destring ptoim1, replace
	gen employed=ptoim1==11 & sykstun!="."

	*Education variables
	g educ=ututku 
	destring educ, replace
	gen level=substr(ututku, 1,1)
	destring level, replace
	
	* Field of study
	gen field=substr(ututku, 2,2)
	destring field, replace
	replace field = 0 if field == .

	* Municipality 
	destring kunta, g(municipality)

	* Earnings
	replace ttyotul = 0 if missing(ttyotul)
	replace tyrtu = 0 if missing(tyrtu)
	replace svatva = 0 if missing(svatva)
	rename svatvp capitalEarnings
	replace capitalEarnings = 0 if missing(capitalEarnings)

	* Sum labor and enrepreneur earnings 
	gen allEarnings =  ttyotul + tyrtu
	
	gen anyEpreneurEarn = tyrtu > 0 


	/* Merge with baseyear, and keep only those individuals that are in the base sample */
	
  	sort shtun
	drop if mi(shtun)
	drop shnro
	
	fmerge 1:1 shtun using "${dataout}police_DV_workplace_defendant_`i'.dta"

	
	keep if _merge==3
    drop _merge
	
	
	gen time = `current'
	
	save "${dataout}DV_workplace_defendant`i'year`a'", replace
	local current = `current' +1 
	}
	
		
}



**** Pull outcomes from the FOLK data for 2017-2019 **** 

forvalues a=2017 (1) 2019{
	
	* folk earnings
	local cond "vuosi==`a'"
	use shnro vuosi tyotu svatva tyrtuo svatvp if `cond' using"D:\ready-made\FOLK_tulo_11a\folk_20112019_tua_tulo21tot_1.dta", clear
	compress
	save "${dataout}FOLK_earnings_`a'.dta", replace

	* folk employment
	local cond "vuosi==`a'"
	use shnro vuosi ptoim1 ammattikoodi_k if `cond' using"D:\ready-made\FOLK_perus_11a\folk_20112020_tua_perus21tot_1.dta", clear
	compress

	merge 1:1 shnro vuosi using "${dataout}FOLK_earnings_`a'.dta"
	
	
	keep if _merge==3
    drop _merge
	
	save "${dataout}FOLK_emp_earnings_`a'.dta", replace
}


forvalues i= 2012(1) 2014{
	local current = 2017-`i'
	while `current' <= 5 {
	local a = `i' + `current'

    use "${dataout}FOLK_emp_earnings_`a'.dta", clear

	
	rename tyotu ttyotul
	rename tyrtuo tyrtu
	
    
	*Employment
	destring ptoim1, replace
	gen employed=ptoim1==11 
	

	* Earnings
	replace ttyotul = 0 if missing(ttyotul)
	replace tyrtu = 0 if missing(tyrtu)
	replace svatva = 0 if missing(svatva)
	rename svatvp capitalEarnings
	replace capitalEarnings = 0 if missing(capitalEarnings)

	* Sum of labor and enrepreneur earnings 
	gen allEarnings =  ttyotul + tyrtu


	/* Merge with baseyear, and keep only those individuals that are in the base sample */
	
	rename shnro defendant_shnro
  	sort defendant_shnro
	drop if mi(defendant_shnro)

	fmerge 1:1 defendant_shnro using "${dataout}police_DV_workplace_defendant_`i'.dta"

	
	keep if _merge==3
    drop _merge
	
	
	gen time = `current'
	
	save "${dataout}DV_workplace_defendant`i'year`a'", replace
	local current = `current' +1 
	}
	
		


	
*** Put together data for same baseyear

	local minus1 = `i' - 1
	local minus2 = `i' - 2
	local minus3 = `i' - 3
	local minus4 = `i' - 4
	local minus5 = `i' - 5
	local plus0 = `i' 
	local plus1 = `i' + 1
	local plus2 = `i' + 2
	local plus3 = `i' + 3
	local plus4 = `i' + 4
	local plus5 = `i' + 5

	*APPEND FILES*
	
	use "${dataout}DV_workplace_defendant`i'year`minus5'.dta", clear
	append using "${dataout}DV_workplace_defendant`i'year`minus4'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`minus3'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`minus2'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`minus1'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`i'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`plus1'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`plus2'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`plus3'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`plus4'.dta"
	append using "${dataout}DV_workplace_defendant`i'year`plus5'.dta"



	*Deflate the earnings 
	g year=vuosi
	fmerge m:1 year using  "${dataout}\cpi"
	keep if _merge == 3 
	drop _merge
	
	gen real_earn=ttyotul/cpi
	gen real_inc=svatva/cpi
	gen realEpreEarn = tyrtu/cpi
	gen realAllEarn = allEarnings/cpi
	gen realCapEarn = capitalEarnings/cpi

	
	drop cpi ttyotul svatva 
		
	*INDUSTRY AND OCCUPATION FOR EWCS DATA
	gen nace_2=real(substr(tol95xht, 1,2))

	loc vars "nace"
	foreach var in `vars' {
		g `var'=.
		replace `var'=1 if `var'_2==1 | `var'_2==2 | `var'_2==5
		replace `var'=2 if `var'_2>=10 & `var'_2<=37
		replace `var'=3 if `var'_2==40 | `var'_2==41
		replace `var'=4 if `var'_2==45  
		replace `var'=5 if `var'_2>=50 & `var'_2<=52
		replace `var'=6 if `var'_2==55  

	}

	drop nace_2 
	label var nace "Industry"
	

	*Occupation codes
	gen occ=real(substr(amko, 1,1))
	
	
	gen baseyr=`i'
	so time baseyr
	order defendant_shnro time baseyr
	
	rename vuosi yr	
	sort defendant_shnro yr
	compress
	save "${dataout}DV_workplace_defendant_`i'.dta", replace
		
}



* Combine all years together
use "W:\workplace\data\DV_workplace_defendant_2006.dta", clear
foreach Z of numlist 2007/2014 {
append using "W:\workplace\data\DV_workplace_defendant_`Z'.dta"
}

rename defendant_sykstun_lag sykstun_lag

keep time baseyr syrtun sykstun ika kunta kunta municipality toimiala ptoim1 ututku amko capitalEarnings tyokk tyrtu tol95xht employed educ level field allEarnings anyEpreneurEarn osalkm tlkm year real_earn real_inc realEpreEarn realAllEarn realCapEarn nace nace occ suspect_sex plaintiff_sex defendant_shnro victim_shnro crimecode6 sykstun_lag

save "W:\workplace\data\DV_workplace_defendant_allyears.dta", replace


cap log close

